package edu.xpu.project.analysis.sql;


/**
 * @describe 构造成绩分析查询sql
 * @author liukang
 * @date 2017年5月28日
 */
public class ScoreAnalysisSql {
	
	public static String getStudentNumByCourseIdAndClassIdGTscore(Long courseId, Long classId, Integer score) {
		String resultSql = "SELECT "
				+ "COUNT(sc.id) "
				+ "FROM "
				+ "	xpu_score_entity sc "
				+ "WHERE "
				+ "	sc.course_id = " + courseId
				+ " AND sc.score_total_val >= " + score
				+ " AND EXISTS ( "
				+ "	SELECT "
				+ "		id "
				+ "	FROM "
				+ "		xpu_user u "
				+ "	WHERE "
				+ "		u.class_id = " + classId
				+"	AND sc.student_id = u.id)";
		return resultSql;
	}
	public static String getStudentNumByCourseIdAndClassIdLTscore(Long courseId, Long classId, Integer score) {
		String resultSql = "SELECT "
				+ "COUNT(sc.id) "
				+ "FROM "
				+ "	xpu_score_entity sc "
				+ "WHERE "
				+ "	sc.course_id = " + courseId
				+ " AND sc.score_total_val <= " + score
				+ " AND EXISTS ( "
				+ "	SELECT "
				+ "		id "
				+ "	FROM "
				+ "		xpu_user u "
				+ "	WHERE "
				+ "		u.class_id = " + classId
				+"	AND sc.student_id = u.id)";
		return resultSql;
	}
	
	/**
	  * @MethodName: getStudentNumByCourseIdAndClassId
	  * @Description: 根据科目id和班级id获取成绩总数
	  * @Author liukang
	  * @date 2017年5月31日  下午12:33:53
	 */
	public static String getStudentNumByCourseIdAndClassId(Long courseId, Long classId) {
		String resultSql = "SELECT COUNT(sc.id) "
						+ "FROM "
						+ "	xpu_score_entity sc "
						+ "WHERE "
						+ "	sc.course_id = " + courseId
						+ " AND EXISTS ( "
						+ "	SELECT "
						+ "		id "
						+ "	FROM "
						+ "		xpu_user u "
						+ "	WHERE "
						+ "		u.class_id = " + classId
						+ "	AND sc.student_id = u.id)";
		return resultSql;
	}
	
	/**
	 * @describe 获取总分的top N
	 * @date 2017年5月29日
	 * @author liukang
	 */
	public static String getSingleTopNByClassIdAndCourseid(Integer n, Long courseId, Long classId) {
		String resultSql = "SELECT class_id, score_total_val, course_id "
				+ "FROM xpu_user u "
				+ "LEFT JOIN xpu_score_entity sc ON sc.student_id = u.id "
				+ "WHERE u.class_id = "+classId+" AND sc.course_id= "+courseId
				+ " ORDER BY sc.score_total_val DESC "
				+ "LIMIT 0, "+n;
		return resultSql;
	}
	
	/**
	 * @describe 获取每个班单科均分sql
	 * @date 2017年5月28日
	 * @author liukang
	 */
	public static String getSingleAvgSql(Long courseId) {
		String getSingleAvgSql = "SELECT"
				+ "	multiTab.class_id AS classId, multiTab.avg AS classAvg, cla.class_name AS className, cla.grade_name AS gradeName "
				+ "FROM "
				+ "("
				+ "SELECT "
				+ "su.class_id,"
				+ "avg(score_total_val) AS avg "
				+ "FROM "
				+ "("
				+ "SELECT "
				+ "u.id,"
				+ "u.class_id,"
				+ "s.score_total_val "
				+ "FROM "
				+ "xpu_score_entity s "
				+ "LEFT JOIN xpu_user u ON u.id = s.student_id "
				+ "WHERE "
				+ "s.course_id=" + courseId
				+ ") su "
				+ "GROUP BY "
				+ "su.class_id"
				+ ") multiTab "
				+ "LEFT JOIN xpu_class cla ON cla.id = multiTab.class_id "
				+ "ORDER BY "
				+ "cla.class_name";
		return getSingleAvgSql;
	}

}
